package com.jm.utils.rule;

import com.jm.utils.StringBuilderEx;
import com.jm.utils.Utils;
import com.jm.web.data.BaseDataEntities;
import com.jm.web.data.BaseDataEntity;
import com.jm.web.data.ReturnData;
import com.jm.utils.StringHelper;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.Vector;

/**
 * @author edward
 *
 */
public class RuleHelper extends BaseRule{

	public RuleHelper(Utils utils) {
		super(utils);
	}
	
	/**
	 * 根據員工工號取得審核人
	 * 
	 * @param hrnum
	 * @param confirmtype
	 * @param verifylev
	 * @deprecated replace by getShrByHrnum(String hrnum,String confirmtype)
	 */
	public BaseDataEntity getShrByHrnum(String hrnum,String confirmtype,String verifylev) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		String sql = StringHelper.Format("select a.tsn as hr005tsn,a.hrnum,a.chname,a.email,(case when d.hrnum is null then isnull(f.tsn,'') else isnull(d.tsn,'') end) as sh_hr005tsn,(case when d.hrnum is null then isnull(f.hrnum,'') else isnull(d.hrnum,'') end) as sh_hrnum,"+
						"(case when d.hrnum is null then isnull(f.chname,'') else isnull(d.chname,'') end) as sh_chname,(case when d.hrnum is null then isnull(f.email,'') else isnull(d.email,'') end) as sh_email from hr..hr005 a left join hr..hr006 b on a.tsn=b.hr005tsn and b.defau=1 " +
						" left join hr..hr016 c on b.hr005tsn=c.hr005tsn1 and c.confirmtype='%1$s' and c.verifylev='%2$s' left join hr..hr005 d on c.hr005tsn2=d.tsn left join hr..hr010 e on b.hr003tsn=e.hr003tsn and e.verifylev='%2$s'" +
						" left join hr..hr005 f on e.hr005tsn=f.tsn where a.hrnum='%3$s'",confirmtype,verifylev,hrnum);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			ret.SetParamValue("confirmtype",confirmtype);
			ret.SetParamValue("verifylev",verifylev);
			ret.SetParamValue("hr005tsn",utils.getRsString(rs,"hr005tsn"));
			ret.SetParamValue("hrnum",utils.getRsString(rs,"hrnum"));
			ret.SetParamValue("chname",utils.getRsString(rs,"chname"));
			ret.SetParamValue("email",utils.getRsString(rs,"email"));
			ret.SetParamValue("sh_hr005tsn",utils.getRsString(rs,"sh_hr005tsn"));
			ret.SetParamValue("sh_hrnum",utils.getRsString(rs,"sh_hrnum"));
			ret.SetParamValue("sh_chname",utils.getRsString(rs,"sh_chname"));
			ret.SetParamValue("sh_email",utils.getRsString(rs,"sh_email"));
		}
		return ret;
	}
	
	/**
	 * 根據員工工號取得審核人
	 * 
	 * @param hrnum 員工工號
	 * @param confirmtype 審核類型
	 */
	public BaseDataEntity getShrByHrnum(String hrnum,String confirmtype) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		StringBuilderEx sh_shr = new StringBuilderEx();//不含代理人
		StringBuilderEx sh_shr_dl = new StringBuilderEx();//含代理人
		String sql = StringHelper.Format("select a.tsn as hr005tsn,a.hrnum,a.chname,a.email," +
				"(case when d.hrnum is null then isnull(f.tsn,'') else isnull(d.tsn,'') end) as sh_hr005tsn1," +
				"(case when d.hrnum is null then isnull(f.hrnum,'') else isnull(d.hrnum,'') end) as sh_hrnum1," +
				"(case when d.hrnum is null then isnull(f.chname,'') else isnull(d.chname,'') end) as sh_chname1," +
				"(case when d.hrnum is null then isnull(f.email,'') else isnull(d.email,'') end) as sh_email1," +
				"(case when h.hrnum is null then isnull(j.tsn,'') else isnull(h.tsn,'') end) as sh_hr005tsn2," +
				"(case when h.hrnum is null then isnull(j.hrnum,'') else isnull(h.hrnum,'') end) as sh_hrnum2," +
				"(case when h.hrnum is null then isnull(j.chname,'') else isnull(h.chname,'') end) as sh_chname2," +
				"(case when h.hrnum is null then isnull(j.email,'') else isnull(h.email,'') end) as sh_email2" +
				" from hr..hr005 a left join hr..hr006 b on a.tsn=b.hr005tsn and b.defau=1" +
				" left join hr..hr016 c on b.hr005tsn=c.hr005tsn1 and c.confirmtype='%1$s' and c.verifylev='1'" +
				" left join hr..hr005 d on c.hr005tsn2=d.tsn left join hr..hr010 e on b.hr003tsn=e.hr003tsn and e.verifylev='1'" +
				" left join hr..hr005 f on e.hr005tsn=f.tsn " +
				" left join hr..hr016 g on b.hr005tsn=g.hr005tsn1 and g.confirmtype='%1$s' and g.verifylev='2'" +
				" left join hr..hr005 h on g.hr005tsn2=h.tsn left join hr..hr010 i on b.hr003tsn=i.hr003tsn and i.verifylev='2'" +
				" left join hr..hr005 j on i.hr005tsn=j.tsn where a.hrnum='%2$s'",confirmtype,hrnum);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			ret.SetParamValue("confirmtype",confirmtype);
			ret.SetParamValue("hr005tsn",utils.getRsString(rs,"hr005tsn"));
			ret.SetParamValue("hrnum",utils.getRsString(rs,"hrnum"));
			ret.SetParamValue("chname",utils.getRsString(rs,"chname"));
			ret.SetParamValue("email",utils.getRsString(rs,"email"));
			ret.SetParamValue("sh_hr005tsn1",utils.getRsString(rs,"sh_hr005tsn1"));
			ret.SetParamValue("sh_hrnum1",utils.getRsString(rs,"sh_hrnum1"));
			ret.SetParamValue("sh_chname1",utils.getRsString(rs,"sh_chname1"));
			ret.SetParamValue("sh_email1",utils.getRsString(rs,"sh_email1"));
			ret.SetParamValue("sh_hr005tsn2",utils.getRsString(rs,"sh_hr005tsn2"));
			ret.SetParamValue("sh_hrnum2",utils.getRsString(rs,"sh_hrnum2"));
			ret.SetParamValue("sh_chname2",utils.getRsString(rs,"sh_chname2"));
			ret.SetParamValue("sh_email2",utils.getRsString(rs,"sh_email2"));
			
			//格式化成 000001|XXX;000002|XXX 的形式
			sh_shr.Append("%1$s|%2$s",utils.getRsString(rs,"sh_hrnum1"),utils.getRsString(rs,"sh_chname1"));				
			if(!utils.getRsString(rs,"sh_hrnum1").equals(utils.getRsString(rs,"sh_hrnum2"))) {//一級二級審核人相同的畫面只要顯示一次就可以
				sh_shr.Append(";%1$s|%2$s",utils.getRsString(rs,"sh_hrnum2"),utils.getRsString(rs,"sh_chname2"));
			}
			ret.SetParamValue("sh_shr",sh_shr.toString());
			
			//取得一二級審核人的代理人
			BaseDataEntity bde = new BaseDataEntity();
			bde = this.getDlrenByHrnum(utils.getRsString(rs,"sh_hrnum1"));
			ret.SetParamValue("dl_hr005tsn1",bde.gpsv("dl_hr005tsn"));
			ret.SetParamValue("dl_hrnum1",bde.gpsv("dl_hrnum"));
			ret.SetParamValue("dl_chname1",bde.gpsv("dl_chname"));
			ret.SetParamValue("dl_email1",bde.gpsv("dl_email"));
			bde = this.getDlrenByHrnum(utils.getRsString(rs,"sh_hrnum2"));
			ret.SetParamValue("dl_hr005tsn2",bde.gpsv("dl_hr005tsn"));
			ret.SetParamValue("dl_hrnum2",bde.gpsv("dl_hrnum"));
			ret.SetParamValue("dl_chname2",bde.gpsv("dl_chname"));
			ret.SetParamValue("dl_email2",bde.gpsv("dl_email"));
			
			//格式化成 000001|XXX(代理:00003|XXX);000002|XXX 的形式
			sh_shr_dl.Append("%1$s|%2$s",utils.getRsString(rs,"sh_hrnum1"),utils.getRsString(rs,"sh_chname1"));
			if(!ret.isNullOrEmpty("dl_hrnum1")) {				
				sh_shr_dl.Append("(代理:%1$s|%2$s)",ret.gpsv("dl_hrnum1"),ret.gpsv("dl_chname1"));
			}
			if(!utils.getRsString(rs,"sh_hrnum1").equals(utils.getRsString(rs,"sh_hrnum2"))) {//一級二級審核人相同的畫面只要顯示一次就可以
				sh_shr_dl.Append(";%1$s|%2$s",utils.getRsString(rs,"sh_hrnum2"),utils.getRsString(rs,"sh_chname2"));				
			}
			if(!ret.isNullOrEmpty("dl_hrnum2")) {				
				sh_shr_dl.Append("(代理:%1$s|%2$s)",ret.gpsv("dl_hrnum2"),ret.gpsv("dl_chname2"));
			}
			ret.SetParamValue("sh_shr",sh_shr_dl.toString());
		}
		return ret;
	}
	
	/**
	 * 根據部門tsn取得審核人
	 * 
	 * @param hr003tsn 部門tsn
	 */
	public BaseDataEntity getShrByHr003tsn (String hr003tsn) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		StringBuilderEx sh_shr = new StringBuilderEx();//不含代理人
		StringBuilderEx sh_shr_dl = new StringBuilderEx();//含代理人
		String sql = StringHelper.Format("select a.tsn as hr003tsn,a.chfullname," +
				"isnull(c.tsn,0) as sh_hr005tsn1,isnull(c.hrnum,'') as sh_hrnum1,isnull(c.chname,'') as sh_chname1,isnull(c.email,0) as sh_email1,"+
				"isnull(e.tsn,0) as sh_hr005tsn2,isnull(e.hrnum,'') as sh_hrnum2,isnull(e.chname,'') as sh_chname2,isnull(e.email,0) as sh_email2 "+
				" from hr..hr003 a left join hr..hr010 b on a.tsn=b.hr003tsn and b.verifylev=1 left join hr..hr005 c on b.hr005tsn=c.tsn"+
				" left join hr..hr010 d on a.tsn=d.hr003tsn and d.verifylev=2 left join hr..hr005 e on d.hr005tsn=e.tsn where a.tsn='%1$s'",hr003tsn);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			ret.SetParamValue("hr003tsn",utils.getRsString(rs,"hr003tsn"));
			ret.SetParamValue("chfullname",utils.getRsString(rs,"chfullname"));
			ret.SetParamValue("sh_hr005tsn1",utils.getRsString(rs,"sh_hr005tsn1"));
			ret.SetParamValue("sh_hrnum1",utils.getRsString(rs,"sh_hrnum1"));
			ret.SetParamValue("sh_chname1",utils.getRsString(rs,"sh_chname1"));
			ret.SetParamValue("sh_email1",utils.getRsString(rs,"sh_email1"));
			ret.SetParamValue("sh_hr005tsn2",utils.getRsString(rs,"sh_hr005tsn2"));
			ret.SetParamValue("sh_hrnum2",utils.getRsString(rs,"sh_hrnum2"));
			ret.SetParamValue("sh_chname2",utils.getRsString(rs,"sh_chname2"));
			ret.SetParamValue("sh_email2",utils.getRsString(rs,"sh_email2"));
			
			//格式化成 000001|XXX;000002|XXX 的形式
			sh_shr.Append("%1$s|%2$s",utils.getRsString(rs,"sh_hrnum1"),utils.getRsString(rs,"sh_chname1"));				
			if(!utils.getRsString(rs,"sh_hrnum1").equals(utils.getRsString(rs,"sh_hrnum2"))) {//一級二級審核人相同的畫面只要顯示一次就可以
				sh_shr.Append(";%1$s|%2$s",utils.getRsString(rs,"sh_hrnum2"),utils.getRsString(rs,"sh_chname2"));
			}
			ret.SetParamValue("sh_shr",sh_shr.toString());
			
			//取得一二級審核人的代理人
			BaseDataEntity bde = new BaseDataEntity();
			bde = this.getDlrenByHrnum(utils.getRsString(rs,"sh_hrnum1"));
			ret.SetParamValue("dl_hr005tsn1",bde.gpsv("dl_hr005tsn"));
			ret.SetParamValue("dl_hrnum1",bde.gpsv("dl_hrnum"));
			ret.SetParamValue("dl_chname1",bde.gpsv("dl_chname"));
			ret.SetParamValue("dl_email1",bde.gpsv("dl_email"));
			bde = this.getDlrenByHrnum(utils.getRsString(rs,"sh_hrnum2"));
			ret.SetParamValue("dl_hr005tsn2",bde.gpsv("dl_hr005tsn"));
			ret.SetParamValue("dl_hrnum2",bde.gpsv("dl_hrnum"));
			ret.SetParamValue("dl_chname2",bde.gpsv("dl_chname"));
			ret.SetParamValue("dl_email2",bde.gpsv("dl_email"));
			
			//格式化成 000001|XXX(代理:00003|XXX);000002|XXX 的形式
			sh_shr_dl.Append("%1$s|%2$s",utils.getRsString(rs,"sh_hrnum1"),utils.getRsString(rs,"sh_chname1"));
			if(!ret.isNullOrEmpty("dl_hrnum1")) {				
				sh_shr_dl.Append("(代理:%1$s|%2$s)",ret.gpsv("dl_hrnum1"),ret.gpsv("dl_chname1"));
			}
			if(!utils.getRsString(rs,"sh_hrnum1").equals(utils.getRsString(rs,"sh_hrnum2"))) {//一級二級審核人相同的畫面只要顯示一次就可以
				sh_shr_dl.Append(";%1$s|%2$s",utils.getRsString(rs,"sh_hrnum2"),utils.getRsString(rs,"sh_chname2"));				
			}
			if(!ret.isNullOrEmpty("dl_hrnum2")) {				
				sh_shr_dl.Append("(代理:%1$s|%2$s)",ret.gpsv("dl_hrnum2"),ret.gpsv("dl_chname2"));
			}
			ret.SetParamValue("sh_shr",sh_shr_dl.toString());
			
		}
		return ret;
	}
	
	/**
	 * 根據員工工號取得處長
	 * 
	 * @param hrnum
	 */
	public BaseDataEntity getCzByHrnum(String hrnum) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		String sql = StringHelper.Format("select a.tsn as hr005tsn,a.hrnum,a.chname,a.email,f.tsn as cz_hr005tsn,f.hrnum as cz_hrnum,f.chname as cz_chname,f.email as cz_email" +
				" from hr..hr005 a,hr..hr006 b,hr..wprg_tmpnew c,hr..hr003 d,hr..hr010 e,hr..hr005 f,hr..hr003 g where a.tsn=b.hr005tsn and b.defau=1 and b.hr003tsn=c.sno1 and c.cmp_nm3=d.chfullname" +
				" and d.tsn=e.hr003tsn and e.hr005tsn=f.tsn and b.hr003tsn=g.tsn and d.enab=2 and d.hr003atsn=g.hr003atsn and e.verifylev=1 and a.hrnum='%1$s'",hrnum);		
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			ret.SetParamValue("hr005tsn",utils.getRsString(rs,"hr005tsn"));
			ret.SetParamValue("hrnum",utils.getRsString(rs,"hrnum"));
			ret.SetParamValue("chname",utils.getRsString(rs,"chname"));
			ret.SetParamValue("email",utils.getRsString(rs,"email"));
			ret.SetParamValue("cz_hr005tsn",utils.getRsString(rs,"cz_hr005tsn"));
			ret.SetParamValue("cz_hrnum",utils.getRsString(rs,"cz_hrnum"));
			ret.SetParamValue("cz_chname",utils.getRsString(rs,"cz_chname"));
			ret.SetParamValue("cz_email",utils.getRsString(rs,"cz_email"));
		}
		return ret; 
	}
	
	/**
	 * 取得離職員工需審核的主管
	 * 
	 * @param hrnum
	 */
	public BaseDataEntity getHr018ZgChk(String hrnum) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		String sql = StringHelper.Format("select a.tsn as hr005tsn,a.hrnum,a.chname,a.email," +
				"(case when d.hrnum is null then isnull(f.tsn,'') else isnull(d.tsn,'') end) as zg_hr005tsn," +
				"(case when d.hrnum is null then isnull(f.hrnum,'') else isnull(d.hrnum,'') end) as zg_hrnum," +
				"(case when d.hrnum is null then isnull(f.chname,'') else isnull(d.chname,'') end) as zg_chname," +
				"(case when d.hrnum is null then isnull(f.email,'') else isnull(d.email,'') end) as zg_email" +
				" from hr..hr005 a left join hr..hr006 b on a.tsn=b.hr005tsn and b.defau=1" +
				" left join hr..hr016 c on b.hr005tsn=c.hr005tsn1 and c.confirmtype='離職' and c.verifylev='1'" +
				" left join hr..hr005 d on c.hr005tsn2=d.tsn left join hr..hr010 e on b.hr003tsn=e.hr003tsn and e.verifylev='1'" +
				" left join hr..hr005 f on e.hr005tsn=f.tsn where a.hrnum='%1$s'",hrnum);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			ret.SetParamValue("zg_hr005tsn",utils.getRsString(rs,"zg_hr005tsn"));
			ret.SetParamValue("zg_hrnum",utils.getRsString(rs,"zg_hrnum"));
			ret.SetParamValue("zg_chname",utils.getRsString(rs,"zg_chname"));
			ret.SetParamValue("zg_email",utils.getRsString(rs,"zg_email"));
		}
		return ret; 
	}
	
	/**
	 * 根據離職員工所在公司別取得可簽核離職的人資人員
	 * 
	 * @param hr003atsn
	 * @param acceptback
	 * @deprecated replace by getHrChkHr005tsn(String typ,String hr003atsn,String typnum)
	 */
	public BaseDataEntity getHr018HrChk(String hr003atsn,String acceptback) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		String sql = StringHelper.Format("select b.tsn as hr_hr005tsn,b.hrnum as hr_hrnum,b.chname as hr_chname,b.email as hr_email from hr..hr018_hrchk a,hr..hr005 b" +
				" where a.hr005tsn=b.tsn and a.hr003atsn='%1$s' and a.acceptback='%2$s' and a.enab=2",hr003atsn,acceptback);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			ret.SetParamValue("hr_hr005tsn",utils.getRsString(rs,"hr_hr005tsn"));
			ret.SetParamValue("hr_hrnum",utils.getRsString(rs,"hr_hrnum"));
			ret.SetParamValue("hr_chname",utils.getRsString(rs,"hr_chname"));
			ret.SetParamValue("hr_email",utils.getRsString(rs,"hr_email"));
		} else {
			ret.SetParamValue("hr_hr005tsn","");
			ret.SetParamValue("hr_hrnum","");
			ret.SetParamValue("hr_chname","");
			ret.SetParamValue("hr_email","");
		}		
		return ret;
	}
	
	/**
	 * 取得人資審核的人員
	 * @param typ 人資審核的類型
	 * @param hr003atsn 人員所在公司別
	 * @param typnum 小類別(離職時表示是否接受迴流)
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntities getHrChkHr005tsn(String typ,String hr003atsn,String typnum) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		StringBuilderEx whr = new StringBuilderEx();
		whr.Append(" or a.typnum='' ");			
		if("離職".equals(typ) && ("21".equals(hr003atsn) || "22".equals(hr003atsn))) {
			whr.Reset();
		}
		String sql = StringHelper.Format("select distinct a.hr005tsn,b.hrnum,b.chname,b.email,case when a.startmail=1 then b.email else 'edward@mail.jmknit.com' end as startmail,b.hrnum+'|'+b.chname as hrShr from hr..hrchk a,hr..hr005 b where a.hr005tsn=b.tsn and a.enab=2 and b.leaveday=''"+
				" and (a.typ='%1$s' or a.typ='') and (a.hr003atsn='%2$s' or ''='%2$s') and (a.typnum='%3$s' or ''='%3$s' %4$s)",typ,hr003atsn,typnum,whr.toString());
		ResultSet rs = utils.executeQuery("info5",sql);
		ret.FromResultSet("key",rs,false);
		BaseDataEntities rows =  (BaseDataEntities)ret.GetParamValue("key");
		//String hr005tsns =rows.ToColumnString("hr005tsn","",",");
		return rows;
	}
	
	/**
	 * 根據hr005tsn取得審核的公司別
	 * @param typ 人資審核的類型
	 * @param hr005tsn 人資人員的tsn
	 * @return
	 * @throws Exception
	 */
	public String getHrChkHr003atsn(String typ,String hr005tsn) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		String sql = StringHelper.Format("select hr003atsn from hr..hrchk where (typ='%1$s' or typ='') and hr005tsn='%2$s' and enab=2",typ,hr005tsn);
		ResultSet rs = utils.executeQuery("info5",sql);
		ret.FromResultSet("key",rs,false);
		BaseDataEntities rows =  (BaseDataEntities)ret.GetParamValue("key");
		String hr003atsns =rows.ToColumnString("hr003atsn","",",");
		return hr003atsns;		
	}
	
	/**
	 * 根據取得sys002中設定的人員
	 * 
	 * @param hr003atsn
	 * @param acceptback
	 */
	public BaseDataEntity getAuthorizedPerson(String sysCode) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		
		return ret; 
	}
	
	/**
	 * 根據人員工號取得人員信息
	 * 
	 * @param hrnum 人員工號
	 */
	public ReturnData getHrInfoByHrnum(String hrnum) throws Exception {
		ReturnData ret = new ReturnData();
		ret = this.getHrInfoByHrnum(hrnum,false,"","",false,false,false,true);		
		return ret; 
	}
	
	/**
	 * 根據人員工號取得人員信息
	 * 
	 * @param hrnum 人員工號
	 * @param onlyHrnum 是否只根據工號來取資料
	 * @param sysCode 特殊人員的權限,空值表示無特殊人員
	 * @param sysHav 特殊人員擁有的權限,格式為"1|2|3|4",1對應havLeave,2對應havLsg,etc,空值表示擁有全部權限,0表示沒有權限
	 * @param havLeave 是否包含離職人員,false只包含在職人員
	 * @param havLsg 是否包含臨時工,false只包含正式工
	 * @param havOtherCmp 是否包含其他公司別的人員,false只包含本公司別的人員
	 * @param havOtherDep 是否包含其他部門的人員,false只包含本部門及子部門的人員
	 */
	public ReturnData getHrInfoByHrnum(String hrnum,boolean onlyHrnum,String sysCode,String sysHav,boolean havLeave,boolean havLsg,boolean havOtherCmp,boolean havOtherDep) throws Exception {
		ReturnData ret = new ReturnData();
		
		StringBuilderEx sb  = new StringBuilderEx();
		sb.Append("select top 1 a.tsn as hr005tsn,a.hrnum,a.chname,(case when a.sex='M' then '男' else '女' end) as sex,a.email,a.idnum,a.idcard,a.backnum,a.jobcode,a.jobcode2,a.jobclass,a.staffid,a.enname,a.addr,a.address2,a.ext,a.tel,a.mob,a.mob2,a.mob3,a.jobsource,a.jobrem,a.js_user_no,"+
				"convert(char(10),a.startday,111) as startday,convert(char(10),a.leaveday,111) as leaveday,(case when a.leaveday='' then 'on' else 'off' end) as isleave,b.hr003tsn,c.chfullname,"+
				"g.tsn as hr003atsn,g.companynamech,h.tsn as thr003atsn,h.companynamech as tcompanynamech,e.tsn as hr001tsn,e.nam,f.tsn as hr002tsn,f.titlename,f.srt,i.tsn as hr011tsn,i.workplace,b.hr004tsn,b.tsn as hr006tsn,"+
				"j.cmp_nm2,j.cmp_nm3,j.cmp_nm4,j.cmp_nm5,j.cmp_nm6,j.dept_name "+
				" from hr..hr005 a,hr..hr006 b,hr..hr003 c,hr..hr004 d,hr..hr001 e,hr..hr002 f,hr..hr003a g,hr..hr003a h,hr..hr011 i,hr..wprg_tmpnew j"+
				" where a.tsn=b.hr005tsn and b.defau=1 and b.hr003tsn=c.tsn and b.hr004tsn=d.tsn and d.hr001tsn=e.tsn "+
				" and a.hr002tsn=f.tsn and c.hr003atsn=g.tsn and b.hr003atsn=h.tsn and a.workplace=i.workplace and c.tsn=j.sno1 ");
		if(onlyHrnum) {
			sb.Append(" and a.hrnum='%1$s'",hrnum);
		} else {
			sb.Append(" and (a.hrnum='%1$s' or a.chname='%1$s' or a.staffid='%1$s' or a.ext='%1$s')",hrnum);
		}
		ResultSet rs = utils.executeQuery("info5",sb.toString());
		if(rs.next()) {
			if(("".equals(sysCode) || !utils.isAuthorized(sysCode) || (!sysHav.contains("1") && !"".equals(sysHav))) 
				&& !havLeave && "off".equals(utils.getRsString(rs,"isleave"))) {
				ret.SetParamValue("errorInfo",utils.i18n("此人已經離職"));
				return ret;				
			}
			if(("".equals(sysCode) || !utils.isAuthorized(sysCode) || (!sysHav.contains("2") && !"".equals(sysHav)))
				&& !havLsg && "3".equals(utils.getRsString(rs,"jobclass"))) {
				ret.SetParamValue("errorInfo",utils.i18n("此人是臨時工"));
				return ret;				
			}
			if(("".equals(sysCode) || !utils.isAuthorized(sysCode) || (!sysHav.contains("3") && !"".equals(sysHav)))
				&& !havOtherCmp && !utils.getHr003aTsn().equals(utils.getRsString(rs,"hr003atsn"))) {
				ret.SetParamValue("errorInfo",utils.i18n("此人非你公司人員"));
				return ret;
			}
			if(("".equals(sysCode) || !utils.isAuthorized(sysCode) || (!sysHav.contains("4") && !"".equals(sysHav)))
				&&!havOtherDep && !utils.getSubHr003Tsn(utils.getHr003Tsn(),"",",",true).contains(utils.getRsString(rs,"hr003tsn"))) {
				ret.SetParamValue("errorInfo",utils.i18n("此人非你部門人員"));
				return ret;
			}
			ret.SetParamValue("errorInfo","");
			//數據
			ret.FromResultSet(rs,false);
			//需翻譯的數據
			ret.SetParamValue("i18n_sex",utils.i18n(utils.getRsString(rs,"sex")));
			ret.SetParamValue("i18n_jobcode",utils.i18n(utils.getRsString(rs,"jobcode")));
			ret.SetParamValue("i18n_chfullname",utils.i18n(utils.getRsString(rs,"chfullname")));
			ret.SetParamValue("i18n_companynamech",utils.i18n(utils.getRsString(rs,"companynamech")));
			ret.SetParamValue("i18n_tcompanynamech",utils.i18n(utils.getRsString(rs,"tcompanynamech")));
			ret.SetParamValue("i18n_nam",utils.i18n(utils.getRsString(rs,"nam")));
			ret.SetParamValue("i18n_titlename",utils.i18n(utils.getRsString(rs,"titlename")));
			ret.SetParamValue("i18n_workplace",utils.i18n(utils.getRsString(rs,"workplace")));
			
		} else {
			ret.SetParamValue("errorInfo",utils.i18n("此工號不存在"));
			return ret;
		}
		return ret;
	}
	
	/**
	 * 根據主管工號其管理的部门,即此人是這些部門的考勤一級審核人
	 * 
	 * @param hrnum 主管工號
	 */
	public String getHr003tsnByZgHrnum(String hrnum) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		String hr003atsns = "";
		String sql = StringHelper.Format("select c.tsn as hr003tsn from hr..hr010 a,hr..hr005 b,hr..hr003 c where a.hr005tsn=b.tsn and a.hr003tsn=c.tsn and a.verifylev=1 and b.hrnum='%1$s'",hrnum);		
		ResultSet rs = utils.executeQuery("info5",sql);
				
		ret.FromResultSet("tsns",rs,false);
		BaseDataEntities rows =  (BaseDataEntities)ret.GetParamValue("tsns");
		hr003atsns = rows.ToColumnString("hr003tsn","",",");
				
		return hr003atsns; 
	}
		
	/**
	 * 根據主管工號取得其管理的人員(hr005tsn)
	 * @param zgHrnum 主管工號
	 * @return
	 * @throws Exception
	 */
	public String getHr005tsnByZgHrnum(String zgHrnum) throws Exception {
		String hr005tsns = "";
		hr005tsns = this.getHrInfoByShrHrnum(zgHrnum,"考勤","1",true).ToColumnString("hr005tsn","",",");
		if(StringHelper.IsNullOrEmpty(hr005tsns)) {			
			hr005tsns = "''";
		}
		return hr005tsns; 
	}
	
	/**
	 * 根據主管工號取得其管理的人員(考勤)(hrnum)
	 * @param zgHrnum 主管工號
	 * @return
	 * @throws Exception
	 */
	public String getHrnumByZgHrnum(String zgHrnum) throws Exception {
		String hrnums = "";
		hrnums = this.getHrInfoByShrHrnum(zgHrnum,"考勤","1",true).ToColumnString("hrnum","'",",");
		if(StringHelper.IsNullOrEmpty(hrnums)) {			
			hrnums = "''";
		}
		return hrnums; 
	}
	
	/**
	 * 根據審核人工號取得其管理的人員(hr005tsn)
	 * @param hrnum 主管工號
	 * @param confirmtype 審核類別
	 * @param verifylev 審核層級
	 * @param havLeave 是否包含離職人員
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntities getHrInfoByShrHrnum(String hrnum,String confirmtype,String verifylev,boolean havLeave) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		StringBuilderEx whr =  new StringBuilderEx("");
		if(!havLeave) {//只包含在職人員
			whr.Append(" and a.leaveday=''");
		}
		//String sql = StringHelper.Format("select a.tsn as hr005tsn,a.hrnum,a.chname,a.email,(case when a.leaveday='' then 'on' else 'off' end) as status from hr..hr005 a,hr..hr016 b,hr..hr005 c " +
				//" where a.tsn=b.hr005tsn1 and b.hr005tsn2=c.tsn and b.confirmtype='%2$s' and b.verifylev='%3$s' and a.jobclass=0 and c.hrnum<>'' and c.hrnum='%1$s' %4$s union"+
				//" select a.tsn as hr005tsn,a.hrnum,a.chname,a.email,(case when a.leaveday='' then 'on' else 'off' end) as status from hr..hr005 a,hr..hr006 b,hr..hr010 c,hr..hr005 d"+ 
				//" where a.tsn=b.hr005tsn and b.defau=1 and b.hr003tsn=c.hr003tsn and c.hr005tsn=d.tsn and c.verifylev='%3$s' and a.jobclass=0 and d.hrnum<>'' and d.hrnum='%1$s' %4$s",hrnum,confirmtype,verifylev,whr.toString());
		String sql = StringHelper.Format("select a.tsn as hr005tsn,a.hrnum,a.chname,a.email,(case when a.leaveday='' then 'on' else 'off' end) as status from"+
					" hr..hr005 a left join hr..hr006 b on a.tsn=b.hr005tsn and b.defau=1"+
					" left join hr..hr010 c on b.hr003tsn=c.hr003tsn and c.verifylev='%3$s'"+
					" left join hr..hr005 d on c.hr005tsn=d.tsn "+
					" left join hr..hr016 e on a.tsn=e.hr005tsn1 and e.verifylev='%3$s' and e.confirmtype='%2$s'"+
					" left join hr..hr005 f on e.hr005tsn2=f.tsn where a.jobclass=0 and d.hrnum<>'' " +
					" and isnull(f.hrnum,d.hrnum)='%1$s' %4$s",hrnum,confirmtype,verifylev,whr.toString());
		ResultSet rs = utils.executeQuery("info5",sql);
		
		ret.FromResultSet("key",rs,false);
		BaseDataEntities rows =  (BaseDataEntities)ret.GetParamValue("key");
		
		return rows; 
	 }
	
	/**
	 * 根據員工工號和日期取得班次
	 * @param hrnum 工號(支持多個工號) eg.'009450','005053',...
	 * @param workday 出勤日期
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntity getBanciByHrnum(String hrnum,String workday) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		hrnum = hrnum.replaceAll(",''","").replaceAll("'',",""); 
		if(StringHelper.IsNullOrEmpty(hrnum)) {
			hrnum = "''";
		}
		if(!hrnum.contains("'")) {			
			hrnum =  StringHelper.Format("'%1$s'",hrnum);
		}
		StringBuilderEx sb =  new StringBuilderEx("");
		sb.Append("select c.hrnum,b.rem from hr..hr030a a,hr..hr029 b,hr..hr005 c,hr..hr006 d");
		sb.Append(" where a.hr029tsn = b.tsn and a.hr005tsn=c.tsn and c.tsn=d.hr005Tsn and d.defau=1");
		sb.Append(" and a.startday<='%1$s' and a.endday>='%1$s' and c.hrNum in (%2$s)",workday,hrnum);
		sb.Append(" union select c.hrnum,b.rem from hr..hr030 a,hr..hr029 b,hr..hr005 c,hr..hr006 d");
		sb.Append(" where a.hr029tsn = b.tsn and a.hr003tsn=d.hr003tsn and c.tsn=d.hr005Tsn and d.defau=1 ");
		sb.Append(" and a.startday<='%1$s' and a.endday>='%1$s' and c.hrNum in (%2$s)",workday,hrnum);
		sb.Append(" and hrnum not in (");
		sb.Append(" select c.hrnum from hr..hr030a a,hr..hr029 b,hr..hr005 c,hr..hr006 d");
		sb.Append(" where a.hr029tsn = b.tsn and a.hr005tsn=c.tsn and c.tsn=d.hr005Tsn and d.defau=1");
		sb.Append(" and a.startday<='%1$s' and a.endday>='%1$s' and c.hrNum in (%2$s))",workday,hrnum);
		ResultSet rs = utils.executeQuery("info5",sb.toString());
		while(rs.next()) {
			ret.SetParamValue(utils.getRsString(rs,"hrnum"),utils.getRsString(rs,"rem"));
		}		
		return ret;
	}
	
	/**
	 * 根據人員工號取得代理人信息
	 * @param hrnum 被代理人工號
	 * @return ret
	 * @throws Exception
	 */
	public BaseDataEntity getDlrenByHrnum(String hrnum) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		StringBuilderEx sb =  new StringBuilderEx("");
		sb.Append("select top 1 c.tsn as dl_hr005tsn,c.hrnum as dl_hrnum,c.chname as dl_chname,c.email as dl_email"); 
		sb.Append(" from hr..hr010a a,hr..hr005 b,hr..hr005 c where a.hr005tsn1=b.tsn and a.hr005tsn2=c.tsn and agentreason>=0");
		sb.Append(" and b.leaveday='' and c.leaveday='' and b.hrnum='%1$s' order by agentreason",hrnum);
		ResultSet rs = utils.executeQuery("info5",sb.toString());
		if(rs.next()) {
			ret.SetParamValue("dl_hr005tsn",utils.getRsString(rs,"dl_hr005tsn"));
			ret.SetParamValue("dl_hrnum",utils.getRsString(rs,"dl_hrnum"));
			ret.SetParamValue("dl_chname",utils.getRsString(rs,"dl_chname"));
			ret.SetParamValue("dl_email",utils.getRsString(rs,"dl_email"));
		}
		return ret;
	}
	
	/**
	 * 根據代理人工號取得被代理人信息
	 * @param hrnum 代理人工號
	 * @return ret
	 * @throws Exception
	 */
	public BaseDataEntity getHrInfoByDlHrnum(String dlhrnum) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		StringBuilderEx sb =  new StringBuilderEx("");
		sb.Append("select top 1 b.tsn as hr005tsn,b.hrnum,b.chname,c.email"); 
		sb.Append(" from hr..hr010a a,hr..hr005 b,hr..hr005 c where a.hr005tsn1=b.tsn and a.hr005tsn2=c.tsn");
		sb.Append(" and b.leaveday='' and c.leaveday='' and c.hrnum='%1$s' order by agentreason",dlhrnum);
		ResultSet rs = utils.executeQuery("info5",sb.toString());
		if(rs.next()) {
			ret.SetParamValue("hr005tsn",utils.getRsString(rs,"hr005tsn"));
			ret.SetParamValue("hrnum",utils.getRsString(rs,"hrnum"));
			ret.SetParamValue("chname",utils.getRsString(rs,"chname"));
			ret.SetParamValue("email",utils.getRsString(rs,"email"));
		}
		return ret;
	}
	
	/**
	 * 根據當天日期取得本月的開始和結束日期
	 * @param today 當天日期,默認以26號作為月份開頭,String格式
	 * @return 返回一個BaseDataEntity,包含(String)startDay和(String)endDay
	 * @throws Exception
	 */
	public BaseDataEntity getStartEndDay(String today) throws Exception {
		return getStartEndDay(new Date(today),26);
	}
	
	/**
	 * 根據當天日期取得本月的開始和結束日期
	 * @param today 當天日期,默認以26號作為月份開頭
	 * @return 返回一個BaseDataEntity,包含(String)startDay和(String)endDay
	 * @throws Exception
	 */
	public BaseDataEntity getStartEndDay(Date today) throws Exception {
		return getStartEndDay(today,26);
	}
	
	/**
	 * 根據當天日期取得本月的開始和結束日期
	 * @param today  當天日期
	 * @param firstDay 當月開始日期,26表示每月26號為開始日期,1表示每月1號為開始日期
	 * @return ret 返回一個BaseDataEntity,包含(String)startDay和(String)endDay
	 * @throws Exception
	 */
	public BaseDataEntity getStartEndDay(Date today,int firstDay) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		//Date startDay = today;
		//Date endDay = today;
		Calendar cal = Calendar.getInstance();
		cal.setTime(today);
		Calendar startDay = Calendar.getInstance();
		startDay.setTime(today);
		Calendar endDay = Calendar.getInstance();
		endDay.setTime(today);
		//int day = today.getDate();
		int day = cal.get(Calendar.DATE);
		if(day >= firstDay) {//肯定是本月的
			startDay.set(Calendar.DATE,firstDay);
			
			endDay.set(Calendar.DATE,firstDay);
			endDay.add(Calendar.MONTH,1);
			endDay.add(Calendar.DATE,-1);
		} else {
			startDay.add(Calendar.MONTH,-1);
			startDay.set(Calendar.DATE,firstDay);
			
			endDay.set(Calendar.DATE,firstDay);
			endDay.add(Calendar.DATE,-1);
		}
		
		ret.SetParamValue("startDay",utils.formatDate(startDay.getTime()));
		ret.SetParamValue("endDay",utils.formatDate(endDay.getTime()));
		
		ret.SetParamValue("startDate",new Date(utils.formatDate(startDay.getTime())));
		ret.SetParamValue("endDate",new Date(utils.formatDate(endDay.getTime())));
		
		return ret;
	}
	
	/**
	 * 根據薪資轉檔時間判斷
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntity getXzZdDay() throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		Calendar cal = Calendar.getInstance();
		int hour = cal.get(Calendar.HOUR);
		int day = cal.get(Calendar.DATE);
		int xzzdday = 0;
		ResultSet rs =utils.executeQuery("info5","select xzzdday from hr..xzzdday");
		if(rs.next()) {
			xzzdday = rs.getInt("xzzdday");				
			if("6".equals(utils.getHr003aTsn())) {
				xzzdday += 1;//越南的加一天
			}
		}
		
		boolean bModXinzi = false;//能否修改當月薪資(如,20120328不可以修改3月份的薪資,20120327 12點前可以)
		boolean bModKaoqin = false;//能否修改當月考勤(如,20120327不可以修改3月份的考勤,20120326可以)
		//判斷當日能否修改考勤
		if(day <= xzzdday-1 || (day == xzzdday && hour <= 11)) {
			cal.add(Calendar.MONTH,-1);
			bModXinzi = true;
		}
		ret = getStartEndDay(cal.getTime(),26);
		ret.SetParamValue("xzzdday",String.valueOf(xzzdday));
		ret.SetParamValue("bModXinzi",bModXinzi);
		//判斷當日能否修改考勤
		if(day <= xzzdday-1) {
			bModKaoqin = true;
		}
		ret.SetParamValue("bModKaoqin",bModKaoqin);
		return ret;
	}
	
	/**
	 * 取得當前傳入日期的前后幾天
	 * @param date 傳入日期
	 * @param daysCount 前後天數
	 * @param havCountryDay 是否包含國假
	 * @param havSat 是否包含星期六
	 * @param havSun 是否包含星期天
	 * @param havWeekDay 是否包含工作日(週一至週五)
	 * @return 具體日期,eg.如2011/12/12(星期一)的前兩天'2011/12/08','2011/12/09'
	 * @throws Exception
	 */
	public BaseDataEntity getBeforeOrAfterDays(Date date,int daysCount,boolean havCountryDay,boolean havSat,boolean havSun,boolean havWeekDay) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		String sql = "";
		ResultSet rs = null;
		Vector vector = new Vector();		
		sql = "select distinct convert(char(10),countryday,111) as countryday from hr..hr027 where holidaynam='國假'";
		rs = utils.executeQuery("info5",sql);
		while(rs.next()) {
			vector.add(utils.getRsString(rs,"countryday"));
		}
		
		LinkedList<String> list = new LinkedList<String>();
		if(daysCount == 0) {
			list.add(utils.formatDate(cal.getTime()));
		} else if(daysCount > 0) {//向后				
			for(int i = 1; i <= daysCount; i++) {
				cal.add(Calendar.DATE,1);
				if(vector.contains((Object)utils.formatDate(cal.getTime()))) {//國假
					if(!havCountryDay) {
						daysCount++;						
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				} else if(Calendar.SUNDAY == cal.get(Calendar.DAY_OF_WEEK)) {//星期日
					if(!havSun) {
						daysCount++;						
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				} else if(Calendar.SATURDAY == cal.get(Calendar.DAY_OF_WEEK)) {//星期六
					if(!havSat) {
						daysCount++;
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				} else if(cal.get(Calendar.DAY_OF_WEEK) >= Calendar.MONDAY && cal.get(Calendar.DAY_OF_WEEK) <= Calendar.FRIDAY) {//星期一至星期五
					if(!havWeekDay) {
						daysCount++;						
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				}
			}
		} else if(daysCount < 0) {//向前				
			for(int i = -1; i >= daysCount; i--) {
				cal.add(Calendar.DATE,-1);
				if(vector.contains((Object)utils.formatDate(cal.getTime()))) {//國假
					if(!havCountryDay) {
						daysCount--;						
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				} else if(Calendar.SUNDAY == cal.get(Calendar.DAY_OF_WEEK)) {//星期日
					if(!havSun) {
						daysCount--;						
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				} else if(Calendar.SATURDAY == cal.get(Calendar.DAY_OF_WEEK)) {//星期六
					if(!havSat) {
						daysCount--;
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				} else if(cal.get(Calendar.DAY_OF_WEEK) >= Calendar.MONDAY && cal.get(Calendar.DAY_OF_WEEK) <= Calendar.FRIDAY) {//星期一至星期五
					if(!havWeekDay) {
						daysCount--;						
					} else {
						list.add(utils.formatDate(cal.getTime()));
					}
				}
			}
		}
		
		String[] args = new String[list.size()];
		list.toArray(args);
		ret.SetParamValue("days",StringHelper.arrayToString(args,"'",","));
		ret.SetParamValue("days_array",args);
		return ret;
	}
		
	/**
	 * 根據部門崗位取得設定的編制人數(正式編制,臨時編制)
	 * @param hr003tsn 部門tsn
	 * @param hr001tsn 崗位tsn
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntity getHrBzNum(String hr003tsn,String hr001tsn) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		int qty = 0;
		int tmpqty = 0;
		int totqty = 0;
		String defaujobcode = "";
		String sql = StringHelper.Format("select * from hr..hr004 where hr003tsn='%1$s' and hr001tsn='%2$s' and enab>0",hr003tsn,hr001tsn);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			qty = rs.getInt("qty");
			tmpqty = rs.getInt("tmpqty");
			totqty = rs.getInt("qty")+rs.getInt("tmpqty");
			defaujobcode = rs.getString("defaujobcode");
		}
		ret.SetParamValue("qty",String.valueOf(qty));
		ret.SetParamValue("tmpqty",String.valueOf(tmpqty));
		ret.SetParamValue("totqty",String.valueOf(totqty));
		ret.SetParamValue("defaujobcode",defaujobcode);
		return ret;
	}
	
	/**
	 * 根據部門崗位取得目前在職的人數
	 * @param hr003tsn 部門tsn
	 * @param hr001tsn 崗位tsn
	 * @param hr005tsn 需排除的人員tsn(通常都是在判斷編制人數時去掉自己,不需排除可以為空)
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntity getHrOnNum(String hr003tsn,String hr001tsn,String hr005tsn) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		int qty = 0;
		String sql = StringHelper.Format("select count(*) as qty from hr..hr005 a,hr..hr006 b,hr..hr004 c,hr..hr003 d,hr..hr001 e"+
					" where a.tsn=b.hr005tsn and b.defau=1 and b.hr004tsn=c.tsn and c.hr003tsn=d.tsn and c.hr001tsn=e.tsn"+
					" and a.leaveday='' and d.tsn='%1$s' and e.tsn='%2$s' and a.tsn<>'%3$s'",hr003tsn,hr001tsn,hr005tsn);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			qty = rs.getInt("qty");
		}
		ret.SetParamValue("qty",String.valueOf(qty));
		return ret;
	}
	
	/**
	 * 取得要調動到此崗位部門的正在調動中的人數
	 * @param hr003tsn
	 * @param hr001tsn
	 * @param hr022tsn 排除的調動單的tsn
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntity getHr022DdingNum(String hr003tsn,String hr001tsn,String hr022tsn) throws Exception {
		BaseDataEntity ret = new BaseDataEntity();
		int qty = 0;
		String sql = StringHelper.Format("select count(*) as qty from hr..hr022 where enab>0 and defau=0 and hr003tsn2='%1$s' and hr001tsn2='%2$s' and tsn<>'%3$s'",hr003tsn,hr001tsn,hr022tsn);
		ResultSet rs = utils.executeQuery("info5",sql);
		if(rs.next()) {
			qty = rs.getInt("qty");
		}
		ret.SetParamValue("qty",String.valueOf(qty));
		return ret;
	}
		
	/**
	 * 根據公司別產生新工號
	 * @param hr003atsn 公司別
	 * @param jobclass 工作身份
	 * @return
	 */
	public String getNewHrnum(String hr003atsn,String jobclass) throws Exception {
		String newHrnum = "";
		String sql = "";
        if("0".equals(jobclass)) {//全職
            if("3".equals(hr003atsn)) {//廣東景鴻,以"H0"開頭
                sql = "select top 1 'H0'+substring(convert(varchar(5),10000+newNO),2,5) as newhrnum from (select top 10000 substring(hrnum,2,6)+1 as newNo from hr..hr005 where hrnum like 'H0%'  order by newid()) a where not newNo in (select substring(hrnum,2,6) from hr..hr005 where hrnum like 'H0%' ) and a.newNo <>'10000'";
            } else if("4".equals(hr003atsn)) {//廣東景信,以"X0"開頭
                sql = "select top 1 'X0'+substring(convert(varchar(5),10000+newNO),2,5) as newhrnum from (select top 10000 substring(hrnum,2,6)+1 as newNo from hr..hr005  where hrnum like 'X0%'  order by newid()) a where not newNo in (select substring(hrnum,2,6) from hr..hr005   where hrnum like 'X0%' ) and not newNo in (select substring(hrnum,2,6) from hr..jxprsn)  and a.newNo <>'10000'";
            } else if("6".equals(hr003atsn)) {//越南,以"N0"開頭
                sql = "select top 1 'N0'+substring(convert(varchar(5),10000+newNO),2,5) as newhrnum from (select top 10000 substring(hrnum,2,6)+1 as newNo from hr..hr005  where hrnum like 'N09%'  order by newid()) a where not newNo in (select substring(hrnum,2,6) from hr..hr005   where hrnum like 'N0%' )  and a.newNo <>'10000'";
            } else if("30".equals(hr003atsn)) {//越南正盛,以"G0"開頭
                sql = "select top 1 'G0'+substring(convert(varchar(5),10000+newNO),2,5) as newhrnum from (select top 10000 substring(hrnum,2,6)+1 as newNo from hr..hr005  where hrnum like 'G0%'  order by newid()) a where not newNo in (select substring(hrnum,2,6) from hr..hr005   where hrnum like 'G0%' ) and a.newNo <>'10000'";
            } else if("21".equals(hr003atsn) || "22".equals(hr003atsn)) {//沭陽,以"05"開頭
            	sql = "select top 1 '0'+convert(varchar(5),newNo) as newhrnum from (select top 10000 hrnum+50000 as newNo from hr..hr005  where hrnum like '00%'  order by newid()) a where not newNo in (select hrnum from hr..hr005   where hrnum like '0%' ) and not newNo in (select '00'+fact_no  from hr..pd_prsn1 where on_job='a'  and cardno<>'')  and newNo <> '10000'  and newNo <> '0000'";
            } else {//景盟(製造+業務)已經其他公司別,以"01"開頭
            	sql = "select top 1 '0'+convert(varchar(5),newNo) as newhrnum from (select top 10000 hrnum+10000 as newNo from hr..hr005  where hrnum like '00%'  order by newid()) a where not newNo in (select hrnum from hr..hr005   where hrnum like '0%' ) and not newNo in (select '00'+fact_no  from hr..pd_prsn1 where on_job='a'  and cardno<>'')  and newNo <> '10000'  and newNo <> '0000'";
            }            
        } else {//臨時工
        	if("21".equals(hr003atsn) || "22".equals(hr003atsn)) {//沭陽(景茂+景晟),以"3"開頭
        		sql = "select max(HRNum+1) as newhrnum from hr..hr005 where hrnum like '3%'";
        	} else if("6".equals(hr003atsn)) {
        		sql = "select max(HRNum+1) as newhrnum from hr..hr005 where hrnum like '2%'";
        	} else {//其他公司別(主要景盟),以"1"開頭
        		sql = "select max(HRNum+1) as newhrnum from hr..hr005 where hrnum like '1%'";
        	}        	
        }
        ResultSet rs = utils.executeQuery("info5", sql);
        if(rs.next()) {
        	newHrnum = utils.getRsString(rs,"newhrnum");
        }
		return newHrnum;
	}
	
	/**
	 * 根據工號取得此人可請假別
	 * @param hrnum 工號
	 * @return
	 * @throws Exception
	 */
	public BaseDataEntities getLeavekindByHrnum(String hrnum) throws Exception {
		BaseDataEntities rets = new BaseDataEntities();
		String sql = StringHelper.Format("select a.tsn,a.leavekind,convert(varchar(10),a.tsn)+'|'+a.leavekind as leavekinds from hr..hr013 a,hr..hr013a b where a.tsn=b.hr013tsn and b.hr003atsn='%1$s' and a.enab=2 and b.enab=2 order by a.tsn",hrnum);
		ResultSet rs = utils.executeQuery("info5",sql);
		rets.FromResultSet(rs);		
		return rets;
	}
	
	/**
	 * 生成新的各種單號
	 * @param server 服務器名稱
	 * @param database 數據庫名稱
	 * @param table	表名稱
	 * @param column 欄位名稱
	 * @param prefix 前綴
	 * @param yymm 日期
	 * @param format 日期格式
	 * @param len 后綴續編部份位數
	 * @return
	 * @throws Exception
	 */
	public String getNewNo(String server,String database,String table,String column,String prefix,String yymm,String format,int len) throws Exception {
		String result = "";
		if("".equals(yymm)) {//取當前年月
			if("".equals(format)) {
				format = "yyyyMM";
			}
			SimpleDateFormat dateFormater = new SimpleDateFormat(format);
			yymm = dateFormater.format(new Date());			
		} else if(yymm == null) {//不需要日期
			yymm = "";
		}
		
		int len1 = len +1;
		String backnum1 = String.valueOf(Math.pow(10,len)+1).replace(".0","");
		String backnum2 = String.valueOf(Math.pow(10,len-1)).replace(".0","").replace("1","0");
		
        String sql = StringHelper.Format("select result='%1$s%2$s'+right(convert(char(%7$s),%8$s+convert(int,right(isnull(max(%5$s),'%1$s%2$s%9$s'),%6$s))),%6$s) from %3$s..%4$s where %5$s like '%1$s%2$s%%'",prefix,yymm,database,table,column,len,len1,backnum1,backnum2);
        ResultSet rs = utils.executeQuery(server,sql);
        if(rs.next()) {        	
        	result = utils.getRsString(rs,"result");
        }
        if(result == null) result = "";
		return result;
	}
	
	/**
	 * @param server
	 * @param database
	 * @param table
	 * @param col
	 * @return
	 * @throws Exception
	 */
	public String getNewIntVal(String server,String database,String table,String column) throws Exception {
		String result = "1";
		String sql = StringHelper.Format("select result=max(%3$s)+1 from %1$s..%2$s",database,table,column);
		ResultSet rs = utils.executeQuery(server,sql);
		if(rs.next()) {
			result = utils.getRsString(rs,"result"); 
		} 
		return result;
	}
}